package co.gov.medellin.ssti.sub.paoyer.administration.dao;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import java.sql.PreparedStatement;

import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.ApplicationVO;

/**
 * DAO para crear, consultar, actualizar y eliminar registros en la tabla
 * TCS_APLICACION.
 * 
 * @author Nicolas.Ceron@consult-soft.com
 * @version 1.0
 */

public class ApplicationDAO {
	/**
	 * Conexion a la base de datos.
	 */
	private Connection conn = null;

	/**
	 * Constructor que recibe como parametro la conexion con la base de datos.
	 * 
	 * @param conn
	 *            Conexion con la base de datos.
	 */

	public ApplicationDAO(Connection conn) {
		this.conn = conn;
	}

	public Collection<ApplicationVO> findApplicationByCriterea(
			ApplicationVO criterea) throws SQLException {

		String query = "SELECT apl.codigo," + "       apl.nombre,"
				+ "       to_char(apl.desde_vigencia,'yyyy-mm-dd') dvig,"
				+ "       to_char(apl.hasta_vigencia,'yyyy-mm-dd') hvig,"
				+ "       apl.vigencia, " + "       apl.sigla,"
				+ "       apl.fecha_crea," + "       apl.usuario_crea,"
				+ "       apl.terminal_crea," + "       apl.fecha_modifica,"
				+ "       apl.usuario_modifica,"
				+ "       apl.terminal_modifica," + "       apl.responsable,"
				+ "       apl.dueno," + "       apl.descripcion,"
				+ "       apl.usu_codigo_identificado_dueno,"
				+ "       apl.usu_codigo" + " FROM vcs_web_aplicaciones apl";

		String filters = "";
		// Filtro por Nombre
		if ((criterea.getName() != null)
				&& !(criterea.getName().equalsIgnoreCase(""))) {
			filters = filters + " UPPER(apl.nombre) LIKE '%"
					+ criterea.getName().toUpperCase() + "%' ";
		}
		// Filtro por Descripcion
		if ((criterea.getDescription() != null)
				&& !(criterea.getDescription().equalsIgnoreCase(""))) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " UPPER(apl.descripcion) LIKE '%"
						+ criterea.getDescription().toUpperCase() + "%' ";
			} else {
				filters = filters + " AND UPPER(apl.descripcion) LIKE '%"
						+ criterea.getDescription().toUpperCase() + "%' ";
			}
		}
		// Filtro por Codigo
		if ((criterea.getCode() != null)
				&& !(criterea.getCode().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " apl.codigo =" + criterea.getCode();
			} else {
				filters = filters + " AND apl.codigo =" + criterea.getCode();
			}
		}

		// Filtro por Vigencia
		if ((criterea.getAbbreviation() != null)
				&& !(criterea.getAbbreviation().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " UPPER(apl.sigla) LIKE '%"
						+ criterea.getAbbreviation().toUpperCase() + "%' ";
			} else {
				filters = filters + " AND UPPER(apl.sigla) LIKE '%"
						+ criterea.getAbbreviation().toUpperCase() + "%' ";
			}
		}
		// Filtro por dueno
		if ((criterea.getOwnerUserCode() != null)
				&& !(criterea.getOwnerUserCode().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " apl.usu_codigo_identificado_dueno ="
						+ criterea.getOwnerUserCode();
			} else {
				filters = filters + " AND apl.usu_codigo_identificado_dueno ="
						+ criterea.getOwnerUserCode();
			}
		}
		// Si hay filtros, entonces armar la clausula WHERE
		if (!filters.equalsIgnoreCase("")) {
			query = query + " where apl.nombre = 'BENEFICIOS' " + filters;
		} else {
			query = query + " where apl.nombre = 'BENEFICIOS' ";
		}

		Statement stm = null;
		ResultSet rst = null;
		ArrayList<ApplicationVO> applications = new ArrayList<ApplicationVO>();
		stm = conn.createStatement();
		rst = stm.executeQuery(query);

		while (rst.next()) {
			ApplicationVO application = new ApplicationVO();
			application.setCode(new Long(rst.getLong("codigo")));
			application.setName(rst.getString("nombre"));
			application.setEffectiveFrom(rst.getString("dvig"));
			application.setEffectiveUntil(rst.getString("hvig"));
			application.setUse(rst.getString("vigencia"));
			application.setAbbreviation(rst.getString("sigla"));
			application.setCreationDate(rst.getDate("fecha_crea"));
			application.setInChargeName(rst.getString("responsable"));
			application.setOwnerName(rst.getString("dueno"));
			application.setDescription(rst.getString("descripcion"));
			application.setOwnerUserCode(new Long(rst
					.getLong("usu_codigo_identificado_dueno")));
			application.setUserCode(new Long(rst.getLong("usu_codigo")));

			applications.add(application);
		}

		stm.close();
		return applications;
	}

	public ApplicationVO createApplication(ApplicationVO application)
			throws SQLException {

		String strSQL = "";
		boolean result = false;
		PreparedStatement ps = null;
		strSQL = ""
				+ "INSERT INTO tcs_aplicacion ("
				+ "            codigo, nombre, descripcion, desde_vigencia, hasta_vigencia, "
				+ "            vigencia, sigla, usu_codigo, usu_codigo_identificado_dueno,"
				+ "            usuario_crea,terminal_crea,usuario_modifica, terminal_modifica) "
				+ "            VALUES(scs_aplicacion.nextval,?,?,to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'),?,?,?,?,?,?,?,?)";

		ps = conn.prepareStatement(strSQL);
		ps.setString(1, application.getName().toUpperCase());
		ps.setString(2, application.getDescription().toUpperCase());
		ps.setString(3, application.getEffectiveFrom());
		ps.setString(4, application.getEffectiveUntil());
		ps.setString(5, application.getUse().toUpperCase());
		ps.setString(6, application.getAbbreviation().toUpperCase());
		ps.setLong(7, application.getUserCode().longValue());
		ps.setLong(8, application.getOwnerUserCode().longValue());
		ps.setString(9, application.getCreationUser().toUpperCase());
		ps.setString(10, "WEB=" + application.getCreationTerminal());
		ps.setString(11, application.getUpdateUser().toUpperCase());
		ps.setString(12, "WEB=" + application.getUpdateTerminal());

		result = ps.execute();

		ps.close();

		return application;
	}

	public int updateApplication(ApplicationVO application) throws SQLException {

		String strSQL = "update tcs_aplicacion " + "set nombre = ?, "
				+ "descripcion = ?, "
				+ "desde_vigencia = to_date(?,'yyyy-mm-dd'), "
				+ "hasta_vigencia = to_date(?,'yyyy-mm-dd'), "
				+ "vigencia = ?, " + "sigla = ?, " + "usuario_modifica = ?, "
				+ "terminal_modifica = ?, " + "usu_codigo = ?, "
				+ "usu_codigo_identificado_dueno = ? " + "where codigo = ?";

		PreparedStatement ps = conn.prepareStatement(strSQL);
		ps.setString(1, application.getName().toUpperCase());
		ps.setString(2, application.getDescription().toUpperCase());
		ps.setString(3, application.getEffectiveFrom());
		ps.setString(4, application.getEffectiveUntil());
		ps.setString(5, application.getUse().toUpperCase());
		ps.setString(6, application.getAbbreviation().toUpperCase());
		ps.setString(7, application.getUpdateUser().toUpperCase());
		ps.setString(8, "WEB=" + application.getUpdateTerminal());
		ps.setLong(9, application.getUserCode().longValue());
		ps.setLong(10, application.getOwnerUserCode().longValue());
		ps.setLong(11, application.getCode().longValue());

		int update = ps.executeUpdate();
		ps.close();
		return update;

	}

	public int deleteApplication(ApplicationVO application) throws SQLException {

		String strSQL = "delete from tcs_aplicacion where codigo = ?";

		PreparedStatement ps = conn.prepareStatement(strSQL);

		ps.setLong(1, application.getCode().longValue());

		int update = ps.executeUpdate();
		ps.close();
		return update;

	}

}